package com.reran.spring.study.homework.h10;

import com.reran.spring.study.homework.h10.model.StudentModel;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;


@Configuration
@SpringBootApplication
public class JdbcBootLauncher {

    public static void main(String[] args) {
        final ConfigurableApplicationContext context = SpringApplication.run(JdbcBootLauncher.class, args);
        // spring boot 默认使用Hikari
        final DataSource dataSource = (DataSource) context.getBean("dataSource");
        //
        Object selectReturn = doSelect(dataSource);
        System.out.println(selectReturn);

//        doInsert(dataSource);
//        doDelete(dataSource);
//        doUpdate(dataSource);
//        doSelect(dataSource);
    }


    /**
     * insert
     *
     * @param dataSource dataSource
     */
    private static void doInsert(DataSource dataSource) {
        System.out.println("doInsert");
        //
        final String sql = "INSERT INTO `user` (`name`, `age`) VALUES (?, ?)";
        executeSql(dataSource, (Connection connection) -> {
            boolean execute = false;
            try {
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, "studentName");
                preparedStatement.setInt(2, 20);
                execute = preparedStatement.execute();
                connection.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return execute;
        });
    }

    /**
     * delete
     *
     * @param dataSource dataSource
     */
    private static void doDelete(DataSource dataSource) {
        System.out.println("doDelete");
        final String sql = "DELETE FROM `user` WHERE id = ?";
        executeSql(dataSource, (Connection connection) -> {
            boolean execute = false;
            try {
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, 1001);
                execute = preparedStatement.execute();
                connection.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return execute;
        });
    }

    /**
     * update
     *
     * @param dataSource dataSource
     */
    private static void doUpdate(DataSource dataSource) {
        System.out.println("doUpdate");
        final String sql = "UPDATE `user` SET `name` = ? WHERE id = ?";
        executeSql(dataSource, (Connection connection) -> {
            boolean execute = false;
            try {
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, "nameAfterChange");
                preparedStatement.setInt(2, 1002);
                execute = preparedStatement.execute();
                connection.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return execute;
        });
    }

    /**
     * select
     *
     * @param dataSource dataSource
     */
    private static Object doSelect(DataSource dataSource) {
        System.out.println("doSelect");
        final String sql = "select `id`,`name`,`age` from `user`";
        final String studentInfo = "student id:%s, name:%s, age:%s";
        final Function<Connection, Object> call = (Connection connection) -> {
            List<StudentModel> result = new ArrayList<>();
            try {
                connection.setAutoCommit(false);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    final Long id = resultSet.getLong(1);
                    final String name = resultSet.getString(2);
                    final Integer age = resultSet.getInt(3);
                    System.out.printf((studentInfo) + "%n", id, name, age);
                    StudentModel studentModel = new StudentModel(id, name, age);
                    result.add(studentModel);
                }
                connection.commit();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return result;
        };

        return executeSql(dataSource, call);
    }

    /**
     * 模版方法，执行SQL
     *
     * @param dataSource dataSource
     * @param consumer   consumer
     */
    private static Object executeSql(DataSource dataSource, Function<Connection, Object> consumer) {
        Connection connection = null;
        Object call = null;
        try {
            connection = dataSource.getConnection();
            call = consumer.apply(connection);
        } catch (Exception e) {
            System.err.println("SQL 执行异常" + e.getMessage());
            if (Objects.nonNull(connection)) {
                try {
                    connection.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            if (Objects.nonNull(connection)) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    System.err.println("connection 关闭异常" + e.getMessage());
                    e.printStackTrace();
                }
            }
        }
        return call;
    }
}
